package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import pojo.Book;
import dao.BookDao;
@Repository
public class BookDaoImpl implements BookDao {
	@Autowired
	private DataSource ds;
	@Override
	public void addBook(Book b) {
		Connection con=null;//数据库连接
		PreparedStatement pst=null;//SQL执行器
		String sql="insert into books1 (id,bname,price) values(john_req.nextval,?,?)";
		
		try {
			con=ds.getConnection();
			pst=con.prepareStatement(sql);
			pst.setString(1, b.getBname());
			pst.setDouble(2, b.getPrice());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	@Override
	public List<Book> findAllBooks() {
		List<Book> books=new ArrayList<Book>();
		Connection con=null;//数据库连接
		PreparedStatement pst=null;//SQL执行器
		ResultSet rs=null;//查出的结果集，以二维表的形式表现出来
		String sql="select * from books1";
		
		try {
			con=ds.getConnection();
			pst=con.prepareStatement(sql);
			rs=pst.executeQuery();
			while(rs.next()){
				int id=rs.getInt("id");
				String bname=rs.getString("bname");
				double price=rs.getDouble("price");
				Book b=new Book(id, bname, price);
				books.add(b);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return books;
	}

	@Override
	public void deleteBook(int id) {
		Connection con=null;//数据库连接
		PreparedStatement pst=null;//SQL执行器
		String sql="delete from books1 where id=?";
		
		try {
			con=ds.getConnection();
			pst=con.prepareStatement(sql);
			pst.setInt(1, id);
			
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				pst.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	public static void main(String[] args) {
		
	}
}
